package com.jiuyun.dao.impi;

import com.jiuyun.dao.BookDao;
import com.jiuyun.entity.Book;
import com.jiuyun.entity.Question;
import com.jiuyun.util.AliPool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class BookDaoImpl implements BookDao {
    static Connection conn;
    static PreparedStatement pst;

    @Override
    public List<Book> allbook() throws SQLException {
        conn= AliPool.getConn();
        String sql="select * from book";
        pst=conn.prepareStatement(sql);
        ResultSet rs=pst.executeQuery();
        List<Book> list=new ArrayList<Book>();
        while (rs.next()){
            int id=rs.getInt(1);
            String name=rs.getString(2);
            String desc=rs.getString(3);
            Book b=new Book(id,name,desc);
            list.add(b);
        }
        conn.close();
        return list;

    }

    @Override
    public List<Question> alltimu(int page,String bookname) throws SQLException {
        if(page<2){
            page=1;
        }
        int n=(page-1)*5;
        conn= AliPool.getConn();
        String sql="SELECT * FROM question WHERE question_type=(SELECT book_id from book WHERE book_name=?) limit ?,5";
        pst=conn.prepareStatement(sql);
        pst.setString(1,bookname);
        pst.setInt(2,n);
        ResultSet rs=pst.executeQuery();
        List<Question> list=new ArrayList<Question>();
        while (rs.next()){
            int q_id=rs.getInt(1);
            String q_name=rs.getString(2);
            int q_score=rs.getInt(3);
            int q_creator=rs.getInt(4);
            int q_type=rs.getInt(5);
            String q_a=rs.getString(6);
            String q_b=rs.getString(7);
            String q_c=rs.getString(8);
            String q_d=rs.getString(9);
            String q_answer=rs.getString(10);
            Date update_time=rs.getDate(11);
            Question q=new Question(q_id,q_name,q_score,q_creator,q_type,q_a,q_b,q_c,q_d,q_answer,update_time);
            list.add(q);
        }
        conn.close();
        return list;
    }

    @Override
    public int count(String bookname) throws SQLException {
        conn=AliPool.getConn();
        String sql="SELECT COUNT(question_id) FROM question  WHERE question_type=(SELECT book_id from book WHERE book_name=?)";
        pst=conn.prepareStatement(sql);
        pst.setString(1,bookname);
        ResultSet rs=pst.executeQuery();
        int count=0;
        while(rs.next()) {
            count=rs.getInt(1);
        }
        conn.close();

        return count;
    }

    @Override
    public int addbook(Book book) throws SQLException {
        conn=AliPool.getConn();
        String sql="insert into book values(null,?,?)";
        pst=conn.prepareStatement(sql);
        pst.setString(1,book.getBookname());
        pst.setString(2,book.getBookdesc());
        int rs=pst.executeUpdate();
        conn.close();
        return rs;
    }

    @Override
    public int upd_bookname(String bookname, int bookid) throws SQLException{
        conn=AliPool.getConn();
        String sql="UPDATE book SET book_name=? where book_id=?";
        pst=conn.prepareStatement(sql);
        pst.setString(1,bookname);
        pst.setInt(2,bookid);
        int rs=pst.executeUpdate();
        conn.close();
        return rs;
    }

    @Override
    public int findbookid(String bookname) throws SQLException {
        conn= AliPool.getConn();
        String sql="select * from book where book_name=?";
        pst=conn.prepareStatement(sql);
        pst.setString(1,bookname);
        ResultSet rs=pst.executeQuery();
        int id=0;
        while (rs.next()){
            id=rs.getInt(1);
        }
        conn.close();
        return id;
    }

    @Override
    public int delbook(int bookid) throws SQLException {
        conn= AliPool.getConn();
        String sql="DELETE FROM book WHERE book_id=?";
        pst=conn.prepareStatement(sql);
        pst.setInt(1,bookid);
        int rs=pst.executeUpdate();

        conn.close();
        return rs;
    }

    @Override
    public int deltimu(int qid) throws SQLException {
        conn= AliPool.getConn();
        String sql="DELETE FROM question WHERE question_id=?";
        pst=conn.prepareStatement(sql);
        pst.setInt(1,qid);
        int rs=pst.executeUpdate();

        conn.close();
        return rs;
    }

    @Override
    public int addtimu(Question question) throws SQLException {
        conn = AliPool.getConn();
        String sql = "INSERT INTO question VALUES(NULL,?,?,?,?,?,?,?,?,?,NOW())";
        pst = conn.prepareStatement(sql);
        pst.setString(1,question.getQ_name());
        pst.setInt(2, question.getQ_score());
        pst.setInt(3, question.getQ_creator());
        pst.setInt(4, question.getQ_type());
        pst.setString(5,question.getQ_a());
        pst.setString(6,question.getQ_b());
        pst.setString(7,question.getQ_c());
        pst.setString(8,question.getQ_d());
        pst.setString(9,question.getQ_answer());
        int rs = pst.executeUpdate();

        conn.close();
        return rs;
    }

    @Override
    public List<Question> findtimu(int qid) throws SQLException {
        conn= AliPool.getConn();
        String sql="SELECT * FROM question WHERE question_id=?";
        pst=conn.prepareStatement(sql);
        pst.setInt(1,qid);
        ResultSet rs=pst.executeQuery();
        List<Question> list=new ArrayList<Question>();
        while (rs.next()){
            int q_id=rs.getInt(1);
            String q_name=rs.getString(2);
            int q_score=rs.getInt(3);
            int q_creator=rs.getInt(4);
            int q_type=rs.getInt(5);
            String q_a=rs.getString(6);
            String q_b=rs.getString(7);
            String q_c=rs.getString(8);
            String q_d=rs.getString(9);
            String q_answer=rs.getString(10);
            Date update_time=rs.getDate(11);
            Question q=new Question(q_id,q_name,q_score,q_creator,q_type,q_a,q_b,q_c,q_d,q_answer,update_time);
            list.add(q);
        }

        conn.close();
        return list;
    }

    @Override
    public int updtimu(Question q) throws SQLException {
        conn = AliPool.getConn();
        String sql = "UPDATE question set question_name=?,question_optiona=?,question_optionb=?,question_optionc=?,question_optiond=?,question_answer=? WHERE question_id=?";
        pst = conn.prepareStatement(sql);
        pst.setString(1,q.getQ_name());
        pst.setString(2,q.getQ_a());
        pst.setString(3,q.getQ_b());
        pst.setString(4,q.getQ_c());
        pst.setString(5,q.getQ_d());
        pst.setString(6,q.getQ_answer());
        pst.setInt(7,q.getQ_id());
        int rs = pst.executeUpdate();

        conn.close();
        return rs;
    }
}
